﻿/******************************************
 * CopyRight:   T2M
 * FileName:    ExcelHelper.cs
 * Author:      Liuxp
 * CreateDate:  2015-05-12 16:00:10
 * Description: Excel 文件操作
 * History:     
 ******************************************/

using System;
using System.Data;
using System.Web;
using System.IO;
using System.Text;
using NPOI.XSSF.UserModel;
using System.Collections.Generic;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.SS.Util;
using NPOI.HPSF;
using System.Collections;
using System.Linq;
using ARchGL.Declaration.Platform.Service;
using ARchGL.Declaration.Platform.Service.Dtos;

/// <summary>
/// NPOIHelper 提供生成Excel文件的方法
/// </summary>
public class NPOIHelper
{
    /// <summary>
    /// 操作
    /// </summary>
    public static string[] OperationList = new string[] { "调整库存", "出库", "变更产品" };

    /// <summary> 
    /// DataTable导出到Excel文件 
    /// </summary> 
    /// <param name="dtSource">源DataTable</param> 
    /// <param name="strHeaderText">表头文本</param> 
    /// <param name="strFileName">保存位置</param> 
    public static void Export(DataTable dtSource, string strHeaderText, string strFileName)
    {
        using (MemoryStream ms = Export(dtSource, strHeaderText))
        {
            using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
            {
                byte[] data = ms.ToArray();
                fs.Write(data, 0, data.Length);
                fs.Flush();
            }
        }
    }

    /// <summary> 
    /// DataTable导出到Excel的MemoryStream  update anyangyang 2013-09-03 升级为npoi 2.0版(.net2.0)
    /// </summary> 
    /// <param name="dtSource">源DataTable</param> 
    /// <param name="strHeaderText">表头文本</param> 
    public static MemoryStream Export(DataTable dtSource, string strHeaderText)
    {
        HSSFWorkbook workbook = new HSSFWorkbook();
        ISheet sheet = workbook.CreateSheet("Sheet1");

        //隐藏指定列
        //sheet.SetColumnHidden(0, true);
        //sheet.SetColumnHidden(1, true);
        //sheet.SetColumnHidden(2, true);
        //sheet.SetColumnHidden(3, true);

        SetDocument(workbook);//右击文件 属性信息

        ICellStyle dateStyle = workbook.CreateCellStyle();
        IDataFormat format = workbook.CreateDataFormat();

        var arrColWidth = new[] { 5, 40, 30, 8, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10 };//自动宽度由于需要打印，所以需要调整为固定宽度

        #region Dropdown list

        //var productList = new ProductionService().GetProductList();
        ////var con = DVConstraint.CreateFormulaListConstraint("OP");
        //var regionsOP = new CellRangeAddressList(0, 65535, 8, 8);
        //var constraintOP = DVConstraint.CreateExplicitListConstraint(OperationList);
        //var rangeOP = workbook.CreateName();
        //for (int i = 0; i < OperationList.Length; i++)
        //{
        //    sheet.CreateRow(2 + i).CreateCell(8).SetCellValue(OperationList[i]);
        //}

        //var productArray = GetProductNameList(productList, Guid.Empty);
        //var regionsName = new CellRangeAddressList(0, 65535, 5, 5);
        //var constraintName = DVConstraint.CreateExplicitListConstraint(productArray);
        //for (int i = 0; i < productArray.Length; i++)
        //{
        //    sheet.GetRow(2 + i).CreateCell(5).SetCellValue(productArray[i]);
        //}
        //var rangeName = workbook.CreateName();

        #endregion

        int rowIndex = 2;
        foreach (DataRow row in dtSource.Rows)
        {
            //rowIndex = 1;

            var areaId = row[0];

            //rangeOP.RefersToFormula = string.Format("Sheet1!$H$8:$H${0}", rowIndex + 1);
            //rangeOP.NameName = "OP";
            //sheet.AddValidationData(new HSSFDataValidation(regionsOP, constraintOP));

            #region 填充内容
            IRow dataRow = sheet.CreateRow(rowIndex);
            ICellStyle style = workbook.CreateCellStyle();
            SetBorder(style);

            foreach (DataColumn column in dtSource.Columns)
            {
                ICell newCell = dataRow.CreateCell(column.Ordinal);

                var drValue = row[column].ToString();

                switch (column.DataType.ToString())
                {
                    case "System.Guid":
                    case "System.String"://字符串类型 
                        newCell.SetCellValue(drValue);
                        break;
                    case "System.DateTime"://日期类型 
                        DateTime dateV;
                        DateTime.TryParse(drValue, out dateV);
                        newCell.SetCellValue(dateV);
                        style.DataFormat = format.GetFormat("yyyy-mm-dd");
                        newCell.CellStyle = style;//格式化显示 
                        break;
                    case "System.Boolean"://布尔型 
                        bool boolV = false;
                        bool.TryParse(drValue, out boolV);
                        newCell.SetCellValue(boolV);
                        break;
                    case "System.Int16"://整型 
                    case "System.Int32":
                    case "System.Int64":
                    case "System.Byte":
                        int intV = 0;
                        int.TryParse(drValue, out intV);
                        newCell.SetCellValue(intV);
                        break;
                    case "System.Decimal"://浮点型 
                    case "System.Double":
                        double doubV = 0;
                        double.TryParse(drValue, out doubV);
                        newCell.SetCellValue(doubV);
                        break;
                    case "System.DBNull"://空值处理 
                        newCell.SetCellValue("");
                        break;
                    default:
                        newCell.SetCellValue("");
                        break;
                }
                newCell.CellStyle = style;
            }
            #endregion

            rowIndex++;

            //rangeName.RefersToFormula = string.Format("Sheet1!$H$5:$H${0}", rowIndex);
            //rangeName.NameName = "Name";
            //sheet.AddValidationData(new HSSFDataValidation(regionsName, constraintName));
        }

        #region 新建表，填充表头，填充列头，样式
        //rowIndex = 0;
        //if (rowIndex == 65535 || rowIndex == 0)
        //{
        //    if (rowIndex != 0) sheet = workbook.CreateSheet();

        #region 表头及样式
        {
            IRow headerRow = sheet.CreateRow(0);
            headerRow.HeightInPoints = 25;
            var cell = headerRow.CreateCell(0);
            cell.SetCellValue(strHeaderText);

            ICellStyle headStyle = workbook.CreateCellStyle();
            headStyle.Alignment = HorizontalAlignment.Center;
            IFont font = workbook.CreateFont();
            font.FontHeightInPoints = 20;
            font.Boldweight = 700;
            headStyle.SetFont(font);
            //SetBorder(headStyle);
            cell.CellStyle = headStyle;
            CellRangeAddress ccc = new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1);
            sheet.AddMergedRegion(ccc);
        }
        #endregion

        #region 列头及样式
        {
            IRow headerRow = sheet.CreateRow(1);
            ICellStyle headStyle = workbook.CreateCellStyle();
            headStyle.Alignment = HorizontalAlignment.Center;
            IFont font = workbook.CreateFont();
            font.FontHeightInPoints = 10;
            font.Boldweight = 700;
            headStyle.SetFont(font);

            foreach (DataColumn column in dtSource.Columns)
            {
                if (column.Ordinal <= (dtSource.Columns.Count - 7)) SetBorder(headStyle);
                var cell = headerRow.CreateCell(column.Ordinal);
                cell.SetCellValue(column.ColumnName);
                cell.CellStyle = headStyle;

                sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);//设置列宽
            }
        }
        #endregion
        //}
        #endregion

        using (MemoryStream ms = new MemoryStream())
        {
            workbook.Write(ms);
            ms.Flush();
            ms.Position = 0;
            //workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet 
            return ms;
        }
    }

    /// <summary>
    /// @author : Liuxp
    /// @datetime: 2013-09-02
    /// @description: 判断单元格内容是否为空
    /// </summary>
    /// <param name="cell"></param>
    /// <returns></returns>
    private static bool IsNullOrEmpty(ICell cell)
    {
        bool result = false;

        if (cell == null || cell.ToString().Trim() == "")
        {
            result = true;
        }
        return result;
    }

    public static void SetDocument(HSSFWorkbook workbook)
    {
        DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
        dsi.Company = "统计报表";
        workbook.DocumentSummaryInformation = dsi;

        SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
        si.Author = "ARchGL"; //填加xls文件作者信息 
        si.ApplicationName = "ARchGL"; //填加xls文件创建程序信息 
        si.LastAuthor = "ARchGL"; //填加xls文件最后保存者信息 
        si.Comments = "ARchGL"; //填加xls文件作者信息 
        si.Title = "统计报表"; //填加xls文件标题信息 
        si.Subject = "统计报表";//填加文件主题信息 
        si.CreateDateTime = DateTime.Now;
        workbook.SummaryInformation = si;
    }

    public static void SetBorder(ICellStyle cell)
    {
        cell.BorderTop = cell.BorderRight = cell.BorderBottom = cell.BorderLeft = BorderStyle.Thin;
    }
}
